﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using SIE.SCADA.SqlSugar;
using Model.Core;

namespace SIE.SCADA.ZemtDemo.Common
{
    public class QueryData
    {
        static PgSqlAccess pgsql = new PgSqlAccess();
        public static smdc_disid_t GetWoInfo(string disidNo)
        {
            smdc_disid_t woinfo = new smdc_disid_t();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    woinfo = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().Where(m => m.disidno == disidNo).First();
                    return woinfo;
                }
            }
            return woinfo;
        }
        
        public static smdc_disid_t GetWorkInfoBySN(string disidNo,string sn)
        {
            smdc_disid_t woinfo = new smdc_disid_t();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    woinfo = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().Where(m => m.disidno == disidNo && m.serial_number==sn).First();
                    return woinfo;
                }
            }
            return woinfo;
        }



        public static List<smdc_disid_t> GetWorkInfoByMatidAndSnAndprocesscode(string matid, string sn,string processcode, string processGroupcode)
        {
            
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    string sql = " 1=1 ";
                    if (matid != null && matid != "") {
                        sql += "and  matid like '%" + matid.Substring(0,12) + "%'    "   ;
                    }
                    if (sn != null && sn != "")
                    {
                        sql += " and serial_number = '" + sn + "' ";
                    }
                    if (processcode == null) processcode = "";
                    if (processGroupcode == null) processGroupcode = "";
                    sql += " and (  workseq = '" + processcode + "' or  disque = '" + processGroupcode + "' ) ";


                    sql += " and state_int = 0 ";



                    var temp =  ormClient.SqlSugarClient.Queryable<smdc_disid_t>().Where(sql).ToList();
                    return temp;
                }
            }
            return null;
        }



        public static List<smdc_disid_t> GetWorkInfoByMatidAndSn(string matid, string sn)
        {

            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    string sql = " 1=1 ";
                    if (matid != null && matid != "")
                    {
                        sql += "and substring(matid from 1 for 12) = '" + matid.Substring(0, 12) + "'    ";
                    }
                    if (sn != null && sn != "")
                    {
                        sql += " and serial_number = '" + sn + "' ";
                    }
                    


                    sql += " and state_int = 0 ";



                    var temp = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().Where(sql).ToList();
                    return temp;
                }
            }
            return null;
        }


        public static List<scada_product_info> GetProdInfoByMatidAndSn(string matid, string sn,string workseqgroup, string repair)
        {

            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    string sql = " 1=1 ";
                    if (matid != null && matid != "")
                    {
                        sql += "and substring(matid from 1 for 12) = '" + matid.Substring(0, 12) + "'    ";
                    }
                    if (sn != null && sn != "")
                    {
                        sql += " and serial_number = '" + sn + "' ";
                    }

                    if (workseqgroup != null && workseqgroup != "")
                    {
                        sql += " and workseqgroup = '" + workseqgroup + "' ";
                    }

                    if (repair != null && repair != "")
                    {
                        sql += " and repaircode = '" + repair + "' ";
                    }


                    sql += " and state_int = 0 ";



                    var temp = ormClient.SqlSugarClient.Queryable<scada_product_info>().Where(sql).OrderBy(m => m.id).ToList();
                    return temp;
                }
            }
            return null;
        }


        public static List<scada_product_info> GetProdInfo(int id)
        {

            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    


                    var temp = ormClient.SqlSugarClient.Queryable<scada_product_info>().Where(m => m.id==id).OrderBy(m => m.id).ToList();
                    return temp;
                }
            }
            return null;
        }



        public static List<scada_disprocess> GetFinishDisProcess(string disNo, string processCode_Str)
        {
            List<scada_disprocess> woinfo = new List<scada_disprocess>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    woinfo = ormClient.SqlSugarClient.Queryable<scada_disprocess>().
                        Where(m => m.disidno == disNo && m.processcode_str == processCode_Str && m.state_int == (int)State.Finish).ToList();
                    return woinfo;
                }
            }
            return woinfo;
        }

        public static List<scada_material_record> GetMaterialRecords(string matid, string sn)
        {
            List<scada_material_record> materials = new List<scada_material_record>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    materials = ormClient.SqlSugarClient.Queryable<scada_material_record>().
                        Where(m => m.matcode == matid && m.sn == sn).ToList();
                    return materials;
                }
            }
            return materials;
        }

        /// <summary>
        /// 检查sn中未完成的工序
        /// </summary>
        /// <param name="sn"></param>
        /// <returns></returns>
        public static List<scada_disprocess> GetSnDisProcess(string sn, string disidNo)
        { 
            List<scada_disprocess> woinfo = new List<scada_disprocess>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    woinfo = ormClient.SqlSugarClient.Queryable<scada_disprocess>().
                        Where(m => m.serial_number_str == sn && m.disidno == disidNo).ToList();
                    return woinfo;
                }
            }
            return woinfo;
        }

        public static List<scada_disprocess> GetSnDisProcess(string sn, string disidNo, string smallVersion)
        {
            List<scada_disprocess> disprocess = new List<scada_disprocess>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    disprocess = ormClient.SqlSugarClient.Queryable<scada_disprocess>().
                        Where(m => m.serial_number_str == sn && m.disidno == disidNo && m.small_version == smallVersion).ToList();
                    return disprocess;
                }
            }
            return disprocess;
        }

        public static Sys_User_UserInfo GetUser(string usercode)
        {
            Sys_User_UserInfo user = new Sys_User_UserInfo();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    user = ormClient.SqlSugarClient.Queryable<Sys_User_UserInfo>().
                        Where(m => m.userid_int == usercode).First();
                }
            }
            return user;
        }

        public static scada_equipment Get_Equipment(string equipId)
        {
            scada_equipment user = new scada_equipment();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    user = ormClient.SqlSugarClient.Queryable<scada_equipment>().
                        Where(m => m.equipmentid == equipId).First();
                }
            }
            return user;
        }

        /// <summary>
        /// 获取记录工序
        /// </summary>
        /// <param name="sn"></param>
        /// <param name="disidNo"></param>
        /// <param name="disqueCode"></param>
        /// <returns></returns>
        public static List<scada_jobstep_record> Get_StepRecord(string sn,string disidNo,string disqueCode)
        {
            var res = new List<scada_jobstep_record>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var process = ormClient.SqlSugarClient.Queryable<scada_process_record>().
                        Where(m => m.disidno == disidNo
                        && m.serial_number == sn
                        && m.disque_code == disqueCode).First();
                    if (process != null)
                    {
                        res = ormClient.SqlSugarClient.Queryable<scada_jobstep_record>().
                            Where(m => m.processid == process.id).ToList();
                    }
                }
            }
            return res;
        }

        internal static scada_MatProductRelation GetMatidByProductid(string id) 
        {
            var res = new scada_MatProductRelation();
            using (var ctx = pgsql.GetClient())
            {

                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var Mat = ormClient.SqlSugarClient.Queryable<scada_MatProductRelation>().
                        Where(m => m.productid == id.Substring(0,12)
                       ).First();
                    return Mat;
                }
            }
            return res; ;
        }


        internal static scada_MatProductRelation GetProductidByMatid(string id)
        {
            var res = new scada_MatProductRelation();
            using (var ctx = pgsql.GetClient())
            {

                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var Mat = ormClient.SqlSugarClient.Queryable<scada_MatProductRelation>().
                        Where(m => m.matid == id
                       ).First();
                    return Mat;
                }
            }
            return res; ;
        }



        public static scada_process_record  Get_ProceRecord(string sn, string disidNo, string disqueCode)
        {
            var res = new scada_process_record();
            using (var ctx = pgsql.GetClient())
            {

                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var process = ormClient.SqlSugarClient.Queryable<scada_process_record>().
                        Where(m => m.disidno == disidNo
                        && m.serial_number == sn
                        && m.disque_code == disqueCode).First();
                    return process;
                }
            }
            return res;
        }


       
        public static List<scada_process> GetProcessListByWorkbinAndPrcessNo(string processNo, string workbin)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process>().
                                    Where(m => m.op_group == processNo && m.workbin == workbin)
                                    .OrderBy(m => m.disque_code)
                                    .ToList();
                    return items ;
                }
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static scada_process GetProcessListByMatIdAndEquipment(string matid, string equipmentCode)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process>().
                                    Where(m => m.matid == matid.Substring(0,12) && m.equipmentCode == equipmentCode)
                                    .OrderBy(m => m.disque_code)
                                    .ToList().FirstOrDefault();
                    return items;
                }
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static scada_process GetProcessByMatIdAndEquipment(string matid, string equipmentCode)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process>().
                                    Where(m => m.matid == matid.Substring(0, 12) && m.equipmentCode == equipmentCode)
                                    .OrderBy(m => m.disque_code)
                                    .ToList().FirstOrDefault();
                    return items;
                }
            }
            return null;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static scada_process GetProcessBySubMatIdAndEquipment(string matid, string equipmentCode)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {

                    string sql = " 1=1 ";
                   
                    sql += "and substring(matid from 1 for 2) = '" + matid.Substring(0, 2) + "'    ";



                    sql += " and equipmentCode = '" + equipmentCode + "' ";



                    var items = ormClient.SqlSugarClient.Queryable<scada_process>().
                                    Where(sql)
                                    .OrderBy(m => m.disque_code)
                                    .ToList().FirstOrDefault();
                    return items;
                }
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<scada_process> GetProcessListByWorkbin(string matid,string workbin)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process>().
                                    Where(m => m.workbin == workbin && m.matid == matid.Substring(0,12))
                                    .OrderBy(m => m.disque_code)
                                    .ToList();
                    return items;
                }
            }
            return null;
        }


        
        public static scada_process_record GetProcessRecordByWorkbIdAndEquipmentcode(int woid, string equipmentcode)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process_record>().
                                    Where(m => m.woid== woid && m.equipmentcode == equipmentcode)
                                    .OrderBy(m => m.disque_code)
                                    .ToList().FirstOrDefault();
                    return items;
                }
            }
            return null;
        }

        
        public static List<scada_process_record> GetProcessRecordByWorkbId(int woid)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process_record>().
                                    Where(m => m.woid == woid )
                                    .OrderBy(m => m.disque_code)
                                    .ToList();
                    return items;
                }
            }
            return null;
        }




        public static List<scada_process_route> GetProcessRouteByMatid(string matid)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<scada_process_route>().
                                    Where(m => m.matid == matid)
                                    .OrderBy(m => m.rank)
                                    .ToList();
                    return items;
                }
            }
            return null;
        }




        public static smdc_disid_t GetWorkInfoBySnAndMatid(string matid,string sn,string disque)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().
                                    Where(m => m.matid == matid && m.serial_number == sn && m.disque == disque).ToList().FirstOrDefault();
                                  ;
                    return items;
                }
            }
            return null;
        }


        public static smdc_disid_t GetWorkInfoBySnAndMatidYYY(string matid, string sn, string disque)
        {
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var items = ormClient.SqlSugarClient.Queryable<smdc_disid_t>().
                                    Where(m => m.matid == matid && m.serial_number == sn && m.disque == disque).ToList().FirstOrDefault();


                    ormClient.SqlSugarClient.SqlQueryable<smdc_disid_t>("select * from smdc_disid_t ");

                    ;
                    return items;
                }
            }
            return null;
        }

        /// <summary>
        /// KJF 20210323 新增
        /// 根据工单号、序列号、子工序号获取ProcessRecord
        /// </summary>
        public static scada_process_record GetProcessRecord(int id)
        {
            //存在则选择未完成的第一个工序
            if (pgsql.DBClient is SmdcOrmClient ormClient)
            {
                var ProcessRecord = ormClient.SqlSugarClient.Queryable<scada_process_record>().
                     Where(m => m.id == id ).ToList().FirstOrDefault();

                return ProcessRecord;
            }
            return null;
        }


        public static List<scada_jobstep_record> GetJobStepByProcessRecordID(int ProcessId)
        {
            List<scada_jobstep_record> JobStepList = new List<scada_jobstep_record>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {

                    JobStepList = ormClient.SqlSugarClient.Queryable<scada_jobstep_record>().
                    Where(m => m.processid == ProcessId)
                    .ToList();

                }
            }
            return JobStepList;
        }


        public static List<scada_jobstep_record> GetJobStepByProdID(int id)
        {
            List<scada_jobstep_record> JobStepList = new List<scada_jobstep_record>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {

                    JobStepList = ormClient.SqlSugarClient.Queryable<scada_jobstep_record>().
                    Where(m => m.prodid == id)
                    .ToList();

                }
            }
            return JobStepList;
        }


        /// <summary>
        /// KJF 2021-03-21  新增
        /// 根据工单号、序列号、工序号获取JobStepRecords
        /// </summary>
        /// <returns></returns>
        public static List<scada_jobstep_record> GetJobStepByWorkIDandSNandProcessCode(string disidNo, string sn, string disque_No)
        {
            List<scada_jobstep_record> JobStepList = new List<scada_jobstep_record>();
            using (var ctx = pgsql.GetClient())
            {
                if (pgsql.DBClient is SmdcOrmClient ormClient)
                {
                    var ProcessRecord = ormClient.SqlSugarClient.Queryable<scada_process_record>().Where(m => m.serial_number == sn
                    && m.disidno == disidNo && m.disque_code == disque_No).ToList().FirstOrDefault();
                    if (ProcessRecord != null)
                    {
                        JobStepList = ormClient.SqlSugarClient.Queryable<scada_jobstep_record>().
                        Where(m => m.processid == ProcessRecord.id)
                        .ToList();
                    }
                }
            }
            return JobStepList;
        }

    }
}
